You can utilize the built-in Excel Worksheet functions such as the VLOOKUP Function, the CHOOSE Function, and the PMT Function in your VBA code and applications as well. In fact, most of the Excel worksheet functions can be accessed and used in VBA code. Like, use INDEX & MATCH Worksheet Functions in Excel VBA code.

Why would you want to use Excel Worksheet functions in your VBA code? Well to extend the functionality of the code you are using. Also, you don’t have to come up with your own functions, unless you really need to, if the functionality is already there. All you basically need to do is access the function you need since it’s already there and there is then no need to reinvent the wheel.

So, let’s get started with an example showing how to use INDEX  MATCH with Excel VBA.

We are going to utilize the INDEX and MATCH Functions in Excel VBA code, in order to create a simple UserForm. Using the form, the user selects the name of the student, and then the corresponding gender of said student and eye color is retrieved and returned.

Please download the practice workbook to practice yourself.

The INDEX and MATCH Functions are often used in combination in formulas, in order to perform advanced lookups. The two in combination offer certain advantages over VLOOKUP.

We have already covered in detail, how to use INDEX and MATCH to perform advanced lookups in an Excel workbook as a straight worksheet formula, in a previous tutorial. We are now going to see how to use the INDEX and MATCH Functions together in VBA code, in order to confer similar functionality to the look up UserForm we are going to create.

Let’s remind ourselves quickly if we wanted to use the INDEX and MATCH Functions in one formula, in the actual worksheet to give us the gender of the name of the student we want to look up. We would use the following formula:

Read More: How to Use INDEX MATCH Formula in Excel (9 Examples)

Read More: INDEX-MATCH with Multiple Matches in Excel (6 Examples)

Read More: How to Create Excel VBA UserForm (with Detailed Steps)

Read More: How to Use INDEX MATCH with Multiple Criteria in Excel (3 Ways)

Similar Readings

Read More: INDEX-MATCH Formula to Find Minimum Value in Excel (4 Ways)

We start off by declaring three variables and assigning the variant data type to these declared variant data types. The variant data type is a good data type to start with. Because when working with worksheet functions, you may not always be sure of the outputs. Therefore use the variant data type, when you are starting out.

Later on, it is advisable to use one of the other more specific data types such as integer or string. For more advanced longer code, the variant data type does not use memory as efficiently as the other data types.

Variable a draw the value from the option the user selects in the drop-down combo box on the UserForm. If there is no selection, then all the other textboxes are empty.

If you select a student name from the combo box on the UserForm, then variable b draws value by using the INDEX Worksheet Function in combination with the MATCH Function in the VBA code, as shown.

It looks up the value using basically the same syntax as the worksheet function.  When using worksheet functions in VBA, the VBA IntelliSense in this particular case is not very intuitive, therefore familiarity with the syntax gleaned from worksheet knowledge is recommended.  Variable c draws value by using the INDEX Worksheet Function in combination with the MATCH Function in the VBA code when the user selects an option from the combo box.

Variable b attains value from the gender column in the worksheet, whereas variable c gets value from the Eye colour column in the worksheet.

The gender textbox is populated with b’s value and the eye color textbox is populated with c’s value.

Read More: Excel VBA Events (A Complete Guideline)

Read More: How to Create a UserForm: an Overview

Read More: How to Use Excel VBA Userform (2 Suitable Examples)

Similar Readings

Remember to save your workbook as a macro-enabled workbook, if you haven’t done so already and there you have it, we use INDEX & MATCH Worksheet Functions in Excel VBA code in order to create a lookup form.

Read More: Excel INDEX-MATCH Formula to Return Multiple Values Horizontally

Excel has many useful worksheet functions, which can be utilized in VBA, as like as, using INDEX & MATCH Worksheet Functions in Excel VBA code. These functions will allow you to extend your VBA code. If you already know how they work in a standard Excel worksheet then the learning curve is not that great by adapting the knowledge for VBA. Accessing the worksheet functions, in one’s VBA code can be a real time-save. Because one does not have to develop custom functions for functionality that is already there.

Please feel free to comment and tell us if you use worksheet functions in your VBA code and applications.

1) Setup a simple list in column A of three items namely tangerines, carrots, and oranges, then in the cell next to each item in column B list whether the items in column A are fruits or vegetables, once you’ve completed setting up your sample data, use the INDEX & MATCH combination function to deliver whether carrots are fruits or vegetables.

2) Use this data set from ESPN on the NFL head coaches and the respective team they are coaching. Create a user form that allows a user to input the name of a certain coach in a textbox. Then have the team he is coaching delivered in another textbox when the user clicks submit. Use INDEX & MATCH worksheet function combination within your VBA code.

Tags: Index Match ExcelVBA in Excel